package day30;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

// JDBC查询操作(从查询结果集中获取数据)
public class Demo02 {
	public static void main(String[] args) {

		test02();

	}

	private static void test02() {
		Connection con = null;
		Statement st = null;
		ResultSet res = null;

		try {

			Class.forName("com.mysql.jdbc.Driver");
			StringBuffer url = new StringBuffer();
			url.append("jdbc:mysql://localhost:3306/demo");
			url.append("?useUnicode=true");
			url.append("&characterEncoding=utf8");
			url.append("&serverTimezone=Asia/Shanghai");
			con = DriverManager.getConnection(url.toString(), "root", "");
			st = con.createStatement();

			String sql = " select id as eid, name, salary from t_emp ";
			// 执行查询语句, 会得到查询结果集对象
			res = st.executeQuery(sql);

			// 遍历查询结果集, 获取每行数据
			while (res.next()) {
				System.out.print(res.getInt("eid") + ", ");
				System.out.print(res.getString("name") + ", ");
				System.out.println(res.getDouble("salary"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (res != null)
					res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (st != null)
						st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (con != null)
							con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

	public static void test01() {
		Connection con = null;
		Statement st = null;
		ResultSet res = null;

		try {

			Class.forName("com.mysql.jdbc.Driver");
			StringBuffer url = new StringBuffer();
			url.append("jdbc:mysql://localhost:3306/demo");
			url.append("?useUnicode=true");
			url.append("&characterEncoding=utf8");
			url.append("&serverTimezone=Asia/Shanghai");
			con = DriverManager.getConnection(url.toString(), "root", "");
			st = con.createStatement();

			String sql = " select id as eid, name, salary from t_emp ";
			// 执行查询语句, 会得到查询结果集对象
			res = st.executeQuery(sql);

			/*
			+------+--------+--------+
			| id   | name   | salary |
			+------+--------+--------+
			| 7369 | smith  |    800 |
			| 7499 | allen  |   1600 |
			| 7521 | ward   |   1250 |
			| 7566 | jones  |   2975 |
			| 7654 | martin |   1250 |
			| 7698 | blake  |   2850 |
			| 7782 | clark  |   2450 |
			| 7788 | scott  |   3000 |
			| 7839 | king   |   5000 |
			| 7844 | turner |   1500 |
			| 7876 | adams  |   1100 |
			| 7900 | james  |    950 |
			| 7902 | ford   |   3000 |
			| 7934 | miller |   1300 |
			| 7935 | alice  |   1234 |
			+------+--------+--------+
			*/

			// 从查询结果集中获取数据
			// next方法: 从结果集中获取一行数据
			// 如果获取到数据, 返回true, 反之则返回false
			boolean flag = res.next();

			// 当获取到一行数据之后, 就可以获取该行数据的各列值

			// 1. 通过列索引获取
			// 注意: JDBC中的索引从1开始
			Object val = res.getObject(1);
			// System.out.println(val);
			// System.out.println(val.getClass());

			val = res.getObject(2);
			// System.out.println(val);
			// System.out.println(val.getClass());

			val = res.getObject(3);
			// System.out.println(val);
			// System.out.println(val.getClass());

			// 数据库中的字段类型在JDBC中都会有默认的对应类型
			// 可以通过默认对应类型获取
			int id = res.getInt(1);
			// System.out.println(id);
			String name = res.getString(2);
			// System.out.println(name);
			double salary = res.getDouble(3);
			// System.out.println(salary);

			// 也可以让JDBC以指定类型获取, JDBC会自动进行类型转换
			String idStr = res.getString(1);
			// System.out.println(idStr);
			// 前提: 所获取的目标数据要能够正常进行类型转换
			// int nameInt = res.getInt(2);
			// System.out.println(nameInt);
			String salaryStr = res.getString(3);
			// System.out.println(salary);
			int salaryInt = res.getInt(3);
			// System.out.println(salaryInt);

			// 2. 通过列标签获取
			// 注意: 这里是通过结果集的列标签, 而不是表的列名
			// 结果集列标签叫什么, 取决于语句是否有取别名
			id = res.getInt("eid");
			System.out.println(id);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (res != null)
					res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (st != null)
						st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (con != null)
							con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
}
